package CelestialDataAccess;

import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception
import java.util.Dictionary;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;

import CelestialDataStructures.*;

public class ProductsDataAccess extends DataAccessBase{

    public static final String PROD_ID = "ProductID";
    public static final String PROD_NAME = "ProductName";
    public static final String CAT = "Category";
    public static final String DESC = "Description";
    public static final String WOOD = "WoodFinish";
    public static final String MAT = "Materials";
    public static final String IMG1 = "Image1";

    public static final String LENGTH = "Length";
    public static final String WIDTH = "Width";
    public static final String HEIGHT = "Height";
    public static final String DIA = "Diameter";

    public static final String PRICE = "Price";
    public static final String UNITS = "Units";
    public static final String SOLD = "Sold";
    public static final String IS_ACTIVE = "IsActive";
    
    /*************************************************************************/
    /* Get / Set methods */
    public SearchCriteria getSearchCriteria(){
        return new ProductsSearchCriteria();
    }
    /*************************************************************************/
    /* Client methods */
    public int insertProduct(Product prod) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspInsertProduct(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

        try{
            stm.setString(PROD_ID, prod.getProductID());
            stm.setString(PROD_NAME, prod.getProductName());
            stm.setString(CAT, prod.getCategory());
            stm.setString(DESC, prod.getDescription());
            stm.setString(MAT, prod.getMaterials());
            stm.setString(WOOD, prod.getWoodFinish());
            stm.setString(IMG1, prod.getImage1());
            
            stm.setDouble(LENGTH, prod.getLength());
            stm.setDouble(WIDTH, prod.getWidth());
            stm.setDouble(HEIGHT, prod.getHeight());
            stm.setDouble(DIA, prod.getDiameter());

            stm.setDouble(PRICE, prod.getPrice());
            stm.setInt(UNITS, prod.getUnits());

            stm.setBoolean(IS_ACTIVE, prod.isActive());

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public int updateProduct(Product prod) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspUpdateProduct(?,?,?,?,?,?,?,?,?,?,?,?,?)}");

        try{
            stm.setString(PROD_ID, prod.getProductID());
            stm.setString(PROD_NAME, prod.getProductName());
            stm.setString(CAT, prod.getCategory());
            stm.setString(DESC, prod.getDescription());
            stm.setString(MAT, prod.getMaterials());
            stm.setString(WOOD, prod.getWoodFinish());
            stm.setString(IMG1, prod.getImage1());
            
            stm.setDouble(LENGTH, prod.getLength());
            stm.setDouble(WIDTH, prod.getWidth());
            stm.setDouble(HEIGHT, prod.getHeight());
            stm.setDouble(DIA, prod.getDiameter());

            stm.setDouble(PRICE, prod.getPrice());

            stm.setBoolean(IS_ACTIVE, prod.isActive());

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public int deleteProduct(String prod_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspDeleteProduct(?)}");

        try{
            stm.setString(PROD_ID, prod_id);
            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public Product getProductByID(String prod_id) throws DataAccessException{
        
        CallableStatement stm = prepareStatement("{CALL uspGetProductByID(?)}");
        CachedRowSet crs; 
  
        DataAccessBase.open();
        try{
            stm.setString(PROD_ID, prod_id);
            crs = getData(stm);
            stm.close();

            crs.last(); //assert 1 row only
            
            if(crs.getRow()==0)
                return null;
            else
                return Product.createProduct(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void getProductsByCategory(String cat, List<Product> lst) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetProductsByCategory(?)}");
        try{
            stm.setString(CAT, cat);
            DataAccessBase.fill(Product.getFactory(), lst, stm);
            stm.close();
        }catch (SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public int transferProduct(String prod_id, int units) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspTransferProduct(?,?)}");

        try{
            stm.setString(PROD_ID, prod_id);
            stm.setInt(UNITS, units);

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public void fill(List<Product> lst) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetProducts}");
        DataAccessBase.fill(Product.getFactory(), lst, stm);
        try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void fill(Dictionary<String, Product> dic) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetProducts}");
        DataAccessBase.fill(Product.getFactory(), dic, stm);
        try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    void searchProducts(String search_sql, List<Product> lst) throws DataAccessException{

         CallableStatement stm = prepareStatement(search_sql);
         DataAccessBase.fill(Product.getFactory(), lst, stm);
         try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void searchProducts(SearchCriteria criteria, List<Product> lst) throws DataAccessException{
        
        if(criteria.getClass()!= ProductsSearchCriteria.class) return;
        
        CallableStatement stm = prepareStatement(criteria.buildSQL());
        DataAccessBase.fill(Product.getFactory(), lst, stm);
        try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void getTopSeller(int rank, List<Product> lst) throws DataAccessException{

        /* clear list */
        lst.clear();

        CallableStatement stm = prepareStatement("{CALL uspGetTopSeller(?)}");
        try{
            stm.setInt("Rank", rank);
            DataAccessBase.fill(Product.getFactory(), lst, stm);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void getTopSellerInCategory(String cat, int rank, List<Product> lst) throws DataAccessException{

        /* clear list */
        lst.clear();

        CallableStatement stm = prepareStatement("{CALL uspGetTopSellerInCategory(?,?)}");
        try{
            stm.setInt("Rank", rank);
            stm.setString(CAT, cat);
            DataAccessBase.fill(Product.getFactory(), lst, stm);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void suggestProductsByCustomerID(int cust_id, List<Product> lst) throws DataAccessException{
        /* clear list */
        lst.clear();

        CallableStatement stm = prepareStatement("{CALL uspSuggestProductsByCustomerID(?)}");
        try{
            stm.setInt("CustomerID", cust_id);
            DataAccessBase.fill(Product.getFactory(), lst, stm);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void suggestProductsByProductID(int cust_id, String prod_id, List<Product> lst) throws DataAccessException{
        /* clear list */
        lst.clear();

        CallableStatement stm = prepareStatement("{CALL uspSuggestProductsByProductID(?,?)}");
        try{
            stm.setInt("CustomerID", cust_id);
            stm.setString(PROD_ID, prod_id);
            DataAccessBase.fill(Product.getFactory(), lst, stm);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*************************************************************************/
    /* Helpers */
    
    /*************************************************************************/
    /* Test */
    public static void main(String[] args){

        ProductsDataAccess daProducts = new ProductsDataAccess();

        List<Product> lst = new ArrayList<Product>();
        Product prod;
        Iterator<Product> it;

        try{
            /* Test Search */
            SearchCriteria criteria = daProducts.getSearchCriteria();
            criteria.setValue("Category", "%Cat1%");
            criteria.setOperator(CAT, SearchCriteria.LIKE);
            daProducts.searchProducts(criteria, lst);

            System.out.println("Test Search");
            it = lst.iterator();
            while(it.hasNext()){
                prod = it.next();
                System.out.println(prod.getProductID() + "\t" + prod.getCategory());
            }

            /* Test get products by category */
            System.out.println("Test By Category");

            daProducts.getProductsByCategory("Cat1.2", lst);
            it = lst.iterator();
            while(it.hasNext()){
                prod = it.next();
                System.out.println(prod.getProductID() + "\t" + prod.getCategory());
            }

            prod = daProducts.getProductByID("din00001");
            System.out.println(prod.getProductID() + "\t" + prod.getProductName() + "\t" +
                                prod.getDescription() + "\t" + PriceFormat.format(prod.getPrice()) + "\t" + prod.getUnits());

            Product p = daProducts.getProductByID("pro00100");
            p.setDescription(("Hello Product"));
            p.setPrice(9999.99);
            p.setHeight(100);
            p.setWidth(100);
            p.setLength(500);
            daProducts.updateProduct(p);
            System.out.println(p.toString(true));

            /* Test top seller */
            //daProducts.suggestProductsByCustomerID(100000, lst);
            daProducts.suggestProductsByProductID(100001, "pro00102", lst);
            for(Product item: lst){
                System.out.println(item + "    "  + item.getSold());
            }
        }catch(DataAccessException ex){
            System.out.println(ex.getMessage());
        }
    }
    /*************************************************************************/
    /*************************************************************************/
    /* Search Criteria */
    private class ProductsSearchCriteria extends SearchCriteria{

        /*********************************************************************/
        /* Constructors */
        ProductsSearchCriteria(){

            m_strSelectClause = "SELECT * FROM vExtendedProducts";

            m_mapValues.put(PROD_ID, null);
            m_mapValues.put(PROD_NAME, null);
            m_mapValues.put(CAT, null);
            m_mapValues.put(DESC, null);
            m_mapValues.put(PRICE, null);
            m_mapValues.put(UNITS, null);

            m_mapOperators.put(PROD_ID, SearchCriteria.EQU);
            m_mapOperators.put(PROD_NAME, SearchCriteria.EQU);
            m_mapOperators.put(CAT, SearchCriteria.EQU);
            m_mapOperators.put(DESC, SearchCriteria.EQU);
            m_mapOperators.put(PRICE, SearchCriteria.EQU);
            m_mapOperators.put(UNITS, SearchCriteria.EQU);
        }
        /*********************************************************************/
    }
}
